package se2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//Created on:	2-Jun-08
//Author:		Richard Shih

public class Customer
{
	private int		id;
	private String	name;
	private String	address;
	private String	postalCode;
	private String	province;
	private String	country;
	private String	email;
	private String	status;

	private String	msg = "";

	public Customer()
	{
	}

	public Customer(int id)
	{
		this.id = id;

		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;

			ps = conn.prepareStatement("select * from CustomerInfo where id = ? ");
			ps.setInt(1, this.id);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				this.setName(rs.getString(2));
				this.setAddress(rs.getString(3));
				this.setEmail(rs.getString(7));
				this.setStatus(rs.getString(8));
			}
			rs.close();
			ps.close();
			conn.close();
		}
		catch (Exception e)
		{
			System.out.println("Query Error in Customer.java - getCustomerFromDB()");
		}
	}

	public String getMsg()
	{
		return msg;
	}

	public void setMsg(String msg)
	{
		this.msg = msg;
	}

	public int getId()
	{
		return id;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	public String getName()
	{
		return name;
	}

	public void setName(String name)
	{
		this.name = name;
	}

	public String getAddress()
	{
		return address;
	}

	public void setAddress(String address)
	{
		this.address = address;
	}

	public String getPostalCode()
	{
		return postalCode;
	}

	public void setPostalCode(String postalCode)
	{
		this.postalCode = postalCode;
	}

	public String getProvince()
	{
		return province;
	}

	public void setProvince(String province)
	{
		this.province = province;
	}

	public String getCountry()
	{
		return country;
	}

	public void setCountry(String country)
	{
		this.country = country;
	}

	public String getEmail()
	{
		return email;
	}

	public void setEmail(String email)
	{
		this.email = email;
	}

	public String getStatus()
	{
		return status;
	}

	public void setStatus(String status)
	{
		this.status = status;
	}

	private Connection getConnection() throws ClassNotFoundException, SQLException
	{
		Class.forName("org.sqlite.JDBC");
		String home = System.getProperty("user.home");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:" + home + "/db/VoIP");

		return conn;
	}

	public boolean addCustomerAccount()
	{

		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;

			ps = conn.prepareStatement("select * from customerinfo where email = ? ");
			ps.setString(1, this.email.toLowerCase());

			ResultSet rs = ps.executeQuery();
			if (rs.next())
			{
				this.id = rs.getInt(1);
				rs.close();
				ps.close();
				conn.close();
				this.msg = "E-mail%20Address%20is%20already%20in%20use";
				return false;
			}
			rs.close();

			ps = conn
					.prepareStatement("insert into customerinfo (name, address, email, status) values ( ? , ? , ? , 'Active' )");
			ps.setString(1, this.name);
			ps.setString(2, this.address);
			ps.setString(3, this.email.toLowerCase());
			ps.executeUpdate();

			ps = conn.prepareStatement("select id from customerinfo where email = ? ");
			ps.setString(1, this.email.toLowerCase());

			this.id = ps.executeQuery().getInt(1);

			rs.close();
			ps.close();
			conn.close();
			return true;
		}
		catch (Exception e)
		{
			System.out.println("Exception HIT in Customer - addCustomerAccount()");
			System.out.println(e);
			return false;
		}
	}

	public List<Customer> getAllCustomerAccounts()
	{
		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;

			ps = conn.prepareStatement("select * from CustomerInfo");

			List<Customer> customerAccounts = new ArrayList<Customer>();
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				Customer ca = new Customer(rs.getInt(1));
				customerAccounts.add(ca);
			}
			rs.close();
			ps.close();
			conn.close();
			return customerAccounts;
		}
		catch (Exception e)
		{
			System.out.println("Query Error in BillingPlan.java - getAllBillingPlans()");
			System.out.println(e);
		}

		return null;
	}

	public boolean cancelCustomer(int id)
	{
		try
		{
			Connection conn = getConnection();
			
			PreparedStatement ps;
			ResultSet rs;
			
			ps = conn.prepareStatement("update CustomerInfo set status='Cancelled' where id = ?");
			ps.setInt(1, id);
			ps.execute();
			
			ps = conn.prepareStatement("select id from PhoneAccount where cust_id = ?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			
			while (rs.next())
			{
				PhoneAccount pa = new PhoneAccount(rs.getInt(1));
				pa.removePhoneAccount();
			}

			return true;
		}
		catch (Exception e)
		{
			System.out.println(e);
			return false;
		}

	}

	public boolean editCustomerAccount(int id, String name, String email, String address)
	{
		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;
			ResultSet rs;

			boolean needToUpdate = false;

			if (!this.email.toLowerCase().equals(email.toLowerCase()))
			{
				ps = conn.prepareStatement("select * from customerinfo where email = ?");
				ps.setString(1, this.email.toLowerCase());
				rs = ps.executeQuery();
				if (rs.next())
				{
					rs.close();
					ps.close();
					conn.close();
					this.msg = "E-mail%20Address%20is%20already%20in%20use";
					return false;
				}
				rs.close();
				ps.close();
				needToUpdate = true;
			}

			if (needToUpdate || !this.name.equals(name) || !this.address.equals(address))
			{
				ps = conn.prepareStatement("update customerinfo set name = ?, email = ?, address = ? where id = ?");
				ps.setString(1, this.name);
				ps.setString(2, this.email.toLowerCase());
				ps.setString(3, this.address);
				ps.setInt(4, this.id);
				ps.executeUpdate();
				ps.close();
			}

			conn.close();
			return true;

		}
		catch (Exception e)
		{
			System.out.println("Exception HIT in Customer - editCustomerAccount()");
			this.msg = "Error%20with%20database";
			System.out.println(e);
			return false;
		}
	}

}
